package com.ctrip.openapi.java.customed;

import java.io.IOException;
import java.io.StringReader;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Timestamp;
import java.text.ParseException;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

import org.jdom.Document;
import org.jdom.Element;
import org.jdom.JDOMException;
import org.jdom.input.SAXBuilder;
import org.jdom.xpath.XPath;
import org.xml.sax.InputSource;

import com.ctrip.openapi.java.base.DBPool;
import com.ctrip.openapi.java.base.DateUtil;
import com.ctrip.openapi.java.base.HttpAccessAdapter;
import com.ctrip.openapi.java.base.model.City;
import com.ctrip.openapi.java.base.model.PriceCache;
import com.ctrip.openapi.java.base.model.PricePlan;
import com.ctrip.openapi.java.base.model.Room;
import com.ctrip.openapi.java.utils.ConfigData;
import com.ctrip.openapi.java.utils.SignatureUtils;

// Http������
public class HotelPriceInfoAdapter {
	
   //ctripinterface 
	private static String createHotelRatePlanRequestXml(List<Room> roomList,String startdate,String enddate){
		StringBuilder sb = new StringBuilder();
		try {
			sb.append("<Request><Header AllianceID=\"");
			sb.append(ConfigData.AllianceId);
			sb.append("\" SID=\"");
			sb.append(ConfigData.SId);
			sb.append("\" TimeStamp=\"");
			long timestamp = SignatureUtils.GetTimeStamp();
			sb.append(timestamp);
			sb.append("\" Signature=\"");

			String signature = SignatureUtils.CalculationSignature(timestamp
					+ "", ConfigData.AllianceId, ConfigData.SecretKey,
					ConfigData.SId, ConfigData.OTA_HotelRatePlan);
			sb.append(signature);
			sb.append("\" RequestType=\"");
			sb.append(ConfigData.OTA_HotelRatePlan);
			sb.append("\" /><HotelRequest><RequestBody xmlns:ns=\"http://www.opentravel.org/OTA/2003/05\" xmlns:xsi=\"http://www.w3.org/2001/XMLSchema-instance\" xmlns:xsd=\"http://www.w3.org/2001/XMLSchema\"><ns:OTA_HotelRatePlanRQ Version=\"1.0\" TimeStamp=\"");
			sb.append(DateUtil.getDateTimeForInterface("yyyy-MM-dd",new Date()));
			sb.append("\" ><ns:RatePlans>");
            for(int i=0;i<roomList.size();i++){
            	sb.append("<ns:RatePlan><ns:DateRange Start=\"");
            	sb.append(startdate);
            	sb.append("\" End=\"");
            	sb.append(enddate);
            	sb.append("\" /><ns:RatePlanCandidates><ns:RatePlanCandidate RatePlanCode=\"");
            	sb.append(roomList.get(i).getRoomTypeCode());
            	sb.append("\" AvailRatesOnlyInd=\"false\" ><ns:HotelRefs><ns:HotelRef HotelCode=\"");
            	sb.append(roomList.get(i).getHotelCode());
            	sb.append("\" /></ns:HotelRefs></ns:RatePlanCandidate></ns:RatePlanCandidates></ns:RatePlan>");
			}
            sb.append("</ns:RatePlans></ns:OTA_HotelRatePlanRQ></RequestBody></HotelRequest></Request>");
		} catch (Exception ex) {
			throw new RuntimeException(ex);
		}

		return sb.toString();
	}
	
	/*
	private static String getFindHotelRatePlan(List<Room> roomList,String startdate,String enddate) {
		String request = createHotelRatePlanRequestXml(roomList,startdate,enddate);
		String url = "http://openapi.ctrip.com/Hotel/OTA_HotelRatePlan.asmx?wsdl";
		String paraName = "requestXML";
		HttpAccessAdapter httpAccessAdapter = new HttpAccessAdapter();
		System.out.println(request);
		String response = httpAccessAdapter.SendRequestToUrl(request, url,
				paraName);
		return response;
	}*/
	
	@SuppressWarnings("unchecked")
	private static List<PricePlan> findHotelRatePlan(List<Room> roomList,String startdate,String enddate){
		
		for(int i=0;i<roomList.size();i++){
			System.out.println(roomList.get(i).getRoomTypeCode()+"-->"+roomList.get(i).getHotelCode());
		}
		List<PricePlan> pricePlanList=new ArrayList<PricePlan>();
		String response=HttpAccessAdapter.getResponseFromCtrip(ConfigData.OTA_HotelRatePlan, HotelPriceInfoAdapter.createHotelRatePlanRequestXml(roomList, startdate, enddate));
		//System.out.println(response);
        StringReader read = new StringReader(response);
        InputSource source = new InputSource(read);
      
        SAXBuilder builder = new SAXBuilder();
        try {      
        	 Document doc = builder.build(source);  
        	 Element root = doc.getRootElement();

        	 XPath rateXPath = XPath.newInstance("//ns:Rate");
        	 rateXPath.addNamespace("ns", "http://www.opentravel.org/OTA/2003/05");
        	
			 List<Element> ratelist =(List<Element>)rateXPath.selectNodes(root);
        	// System.out.println("lala");
        	 
        	 for(int i=0;i<ratelist.size();i++){
        		 PricePlan pricePlan=new PricePlan();
        		 Element rate=ratelist.get(i);
        		 String tstamp=rate.getParentElement().getParentElement().getParentElement().getParentElement().getAttributeValue("TimeStamp");
        		// System.out.println(tstamp);
        		/* System.out.println(rate.getParentElement().getParentElement().getAttributeValue("RatePlanCode")
        				 +"---"+rate.getParentElement().getParentElement().getParentElement().getAttributeValue("HotelCode")+"-->"
        				 +rate.getAttributeValue("Start")+"-->"
        				 +tstamp.substring(0,tstamp.lastIndexOf("+")).replace("T", " ").replace(".", "[.")+"]");*/
        		 tstamp=tstamp.substring(0,tstamp.lastIndexOf(".")).replace("T", " ");
        		 System.out.println(tstamp);
        		 pricePlan.setUpdateTime(tstamp);
        		 pricePlan.setHotelCode(rate.getParentElement().getParentElement().getParentElement().getAttributeValue("HotelCode"));
        		 pricePlan.setRatePlanCode(rate.getParentElement().getParentElement().getAttributeValue("RatePlanCode"));
    			 pricePlan.setRatePlanCategory(rate.getParentElement().getParentElement().getAttributeValue("RatePlanCategory"));
    			 pricePlan.setMarketCode(rate.getParentElement().getParentElement().getAttributeValue("MarketCode"));
    			 pricePlan.setStart(rate.getAttributeValue("Start"));
    			 pricePlan.setEnd(rate.getAttributeValue("End"));
    			 pricePlan.setStatus(rate.getAttributeValue("Status"));
    			 
    			 List<Element> rateChildren =rate.getChildren();
    			 for(int j=0;j<rateChildren.size();j++){
    				 Element rateChild=rateChildren.get(j);
    				 if("BaseByGuestAmts".equals(rateChild.getName())){
    					 
    					 pricePlan.setAmountBeforeTax(((Element)(rateChild.getChildren().get(0))).getAttributeValue("AmountBeforeTax"));
    					 pricePlan.setCurrencyCode(((Element)(rateChild.getChildren().get(0))).getAttributeValue("CurrencyCode"));
    					 pricePlan.setNumberOfGuests(((Element)(rateChild.getChildren().get(0))).getAttributeValue("NumberOfGuests"));
    					
    				 }else if("GuaranteePolicies".equals(rateChild.getName())){
    					 pricePlan.setGuaranteeCode(((Element)(rateChild.getChildren().get(0))).getAttributeValue("GuaranteeCode"));
    				 }else if("CancelPolicies".equals(rateChild.getName())){
    					 Element cancelPolicies=(Element)(rateChild.getChildren().get(0));
    					 pricePlan.setCancelPenaltyStart(cancelPolicies.getAttributeValue("Start"));
    					 pricePlan.setCancelPenaltyEnd(cancelPolicies.getAttributeValue("End"));
    					 pricePlan.setCancelPenalty(((Element)(cancelPolicies.getChildren().get(0))).getAttributeValue("Amount"));//
    					 pricePlan.setListPrice(((Element)(cancelPolicies.getChildren().get(0))).getAttributeValue("ListPrice"));
    					 pricePlan.setCurrencyCode(((Element)(cancelPolicies.getChildren().get(0))).getAttributeValue("CurrencyCode"));//
    				 }else if("TPA_Extensions".equals(rateChild.getName())){
    					 Element rebatePromotion =(Element)(rateChild.getChildren().get(0));
    					 pricePlan.setRebatePromotionCode(rebatePromotion.getAttributeValue("rebatePromotionCode"));
    					 pricePlan.setRebatePromotionCurrencyAmount(rebatePromotion.getAttributeValue("Amount"));
    					 pricePlan.setRebatePromotionCurrencyCode(rebatePromotion.getAttributeValue("CurrencyCode"));
    					 pricePlan.setRebatePromotionStart(rebatePromotion.getAttributeValue("StartPeriod"));
    					 pricePlan.setRebatePromotionEnd(rebatePromotion.getAttributeValue("EndPeriod"));
    					 pricePlan.setRebatePromotionName(rebatePromotion.getAttributeValue("ProgramName"));
    					 Element desc =(Element)(rebatePromotion.getChildren().get(0));
    					 Element text =(Element)(desc.getChildren().get(0));
    					 pricePlan.setRebatePromotionDesc(text.getText());
    				 }
    				 
    			 }		 
        		pricePlanList.add(pricePlan);
        	 }
        	 
        	
        }catch (JDOMException e) {    
    		e.printStackTrace(); 
    		} catch (IOException e) {  
    			e.printStackTrace(); 
    			}
       return pricePlanList;
	}

	private static List<City> getCitiesInAllRooms() throws SQLException{

		List<City> clist=new ArrayList<City>();
		/*Room r=new Room();
		r.setHotelCode("5");
		r.setRoomTypeCode("5505");1--------1-10    2--------------11-20
		roomlist.add(r);*/
		Connection con = null;
		java.sql.Statement st = null;
		con = DBPool.getConnection();
		
		//con.
		if (con != null)
			{
			st=con.createStatement();
			ResultSet rs=st.executeQuery("SELECT distinct h.hotelCityCode FROM hotelroom h order by h.hotelCityCode+1");  
			while(rs.next()){
				City r=new City();
			r.setCity(rs.getString("hotelCityCode"));
				clist.add(r);	
			}
		}
		st.close();
		con.close();
		return clist;
	}
	private static List<Room> getAllRooms(String cityCode) throws SQLException{
	
		
		List<Room> roomlist=new ArrayList<Room>();
		/*Room r=new Room();
		r.setHotelCode("5");
		r.setRoomTypeCode("5505");1--------1-10    2--------------11-20
		roomlist.add(r);*/
		Connection con = null;
		java.sql.Statement st = null;
		con = DBPool.getConnection();
		
		//con.
		if (con != null)
			{
			st=con.createStatement();
			ResultSet rs=st.executeQuery("SELECT h.roomTypeCode,h.hotelCode,h.hotelCityCode FROM hotel.hotelroom  h " +
					"where h.hotelCityCode='"+cityCode+"' order by hotelCityCode+1,hotelCode+1");  
			
			while(rs.next()){
				Room r=new Room();
				r.setHotelCode(rs.getString("hotelCode"));
				r.setRoomTypeCode(rs.getString("roomTypeCode"));//
				r.setHotelCityCode(rs.getString("hotelCityCode"));
				roomlist.add(r);	
			}
		}
		st.close();
		con.close();
		return roomlist;
	}
	

	
	private static void jdbcPlanRooms(List roomlist,String startdate,String enddate,String cityCode) throws SQLException{
		Connection con = null;
		con = DBPool.getConnection();
		con.setAutoCommit(false);
		java.sql.PreparedStatement st1 = null;
		if (con != null)
		{System.out.println("connection get....................ok");}
		//489219  12543  10拼装一次报文  roomlist 
		List<PricePlan> pp=HotelPriceInfoAdapter.findHotelRatePlan(roomlist,startdate,enddate);
		
		
	
		st1 = con.prepareStatement("insert into priceplan"+cityCode+"(ratePlanCode,hotelCode,ratePlanCategory,marketCode,minAdvancedBookingOffset," +
				"starttime,endtime,status,amountBeforeTax,currencyCode,listPrice,numberOfGuests,cancelPenalty,cancelPenaltyStart,cancelPenaltyEnd," +
				"mealsIncluded,rebatePromotionStart,rebatePromotionEnd,rebatePromotionName,rebatePromotionCode," +
				"rebatePromotionCurrencyCode,rebatePromotionCurrencyAmount,rebatePromotionDesc,guaranteeCode,updatePlanTime)"
				+ " values(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)");
		
		for (int row = 0; row < pp.size(); row++) {
		st1.setString(1, pp.get(row).getRatePlanCode());st1.setString(2, pp.get(row).getHotelCode());
		st1.setString(3, pp.get(row).getRatePlanCategory());st1.setString(4, pp.get(row).getMarketCode());
		st1.setString(5, pp.get(row).getMinAdvancedBookingOffset());st1.setString(6, pp.get(row).getStart());
		st1.setString(7, pp.get(row).getEnd());st1.setString(8, pp.get(row).getStatus());
		st1.setString(9, pp.get(row).getAmountBeforeTax());st1.setString(10, pp.get(row).getCurrencyCode());
		st1.setString(11, pp.get(row).getListPrice());st1.setString(12, pp.get(row).getNumberOfGuests());
		st1.setString(13, pp.get(row).getCancelPenalty());st1.setString(14, pp.get(row).getCancelPenaltyStart());
		st1.setString(15, pp.get(row).getCancelPenaltyEnd());st1.setString(16, pp.get(row).getMealsIncluded());
		st1.setString(17, pp.get(row).getRebatePromotionStart());st1.setString(18, pp.get(row).getRebatePromotionEnd());
		st1.setString(19, pp.get(row).getRebatePromotionName());st1.setString(20, pp.get(row).getRebatePromotionCode());
		st1.setString(21, pp.get(row).getRebatePromotionCurrencyCode());st1.setString(22, pp.get(row).getRebatePromotionCurrencyAmount());
		st1.setString(23, pp.get(row).getRebatePromotionDesc());st1.setString(24, pp.get(row).getGuaranteeCode());
		//System.out.println(pp.get(row).getUpdateTime());
		st1.setTimestamp(25, Timestamp.valueOf(pp.get(row).getUpdateTime()));
		st1.addBatch();
		}
		st1.executeBatch();
		st1.clearBatch();
		
		con.commit();
		st1.close();
		con.close();
	}
	
	private static void sychronizePlanRooms(String startdate,String enddate) throws SQLException{
		//Long beginTime = System.currentTimeMillis();
		String datestart1=DateUtil.getDateTimeForInterface("yyyy-MM-dd",new Date());
		String dateend1=DateUtil.getDateTimeForInterface("yyyy-MM-dd",DateUtil.addDaysToDate(new Date(), 3));
		/*String datestart2=DateUtil.getDateTimeForInterface("yyyy-MM-dd",DateUtil.addDaysToDate(new Date(), 29));
		String dateend2=DateUtil.getDateTimeForInterface("yyyy-MM-dd",DateUtil.addDaysToDate(new Date(), 56));
		String datestart3=DateUtil.getDateTimeForInterface("yyyy-MM-dd",DateUtil.addDaysToDate(new Date(), 57));
		String dateend3=DateUtil.getDateTimeForInterface("yyyy-MM-dd",DateUtil.addDaysToDate(new Date(), 84));
		*/
		
		String cityCode="1";
		//city--->roomlist
		List<City> clist=HotelPriceInfoAdapter.getCitiesInAllRooms();
		for(int m=0;m<clist.size();m++){
			List<Room> roomlist=HotelPriceInfoAdapter.getAllRooms(clist.get(m).getCity());
			int page= (roomlist.size()%ConfigData.pageSize==0)?roomlist.size()/ConfigData.pageSize:(roomlist.size()/ConfigData.pageSize+1);
	        //int start=0;//int end=0+;   961    97       10
			for(int i=0;i<page;i++){
				if(i==page){
					HotelPriceInfoAdapter.jdbcPlanRooms(roomlist.subList(ConfigData.pageSize*i, roomlist.size()),datestart1,dateend1,clist.get(i).getCity());
					//HttpAccessAdapter.jdbcPlanRooms(roomlist.subList(ConfigData.pageSize*i, roomlist.size()),datestart2,dateend2);
					//HttpAccessAdapter.jdbcPlanRooms(roomlist.subList(ConfigData.pageSize*i, roomlist.size()),datestart3,dateend3);
				}else{
					HotelPriceInfoAdapter.jdbcPlanRooms(roomlist.subList(ConfigData.pageSize*i, ConfigData.pageSize*(i+1)),datestart1,dateend1,clist.get(i).getCity());
					//HttpAccessAdapter.jdbcPlanRooms(roomlist.subList(ConfigData.pageSize*i, ConfigData.pageSize*(i+1)),datestart2,dateend2);
					//HttpAccessAdapter.jdbcPlanRooms(roomlist.subList(ConfigData.pageSize*i, ConfigData.pageSize*(i+1)),datestart3,dateend3);
				}
				
			}
			//每个城市最后一次时间
			insertLastUpdateTime(clist.get(m).getCity());
			insertLastUpdateTime(clist.get(m).getCity());
		}		
	}
	/*
	private static void deleteLastUpdateTime(String cityCode) throws SQLException{
		Connection con = null;
		con = DBPool.getConnection();
		//con.setAutoCommit(false);
		java.sql.Statement st1 = null;
		if (con != null)
		{System.out.println("connection get....................ok");}
		//删除本城市记录然后插入新
		 st1.executeUpdate("delete from updaterecord where cityCode ='"+cityCode+"')");
		 con.commit();
			st1.close();
			con.close();
	}*/
	private static void insertLastUpdateTime(String cityCode) throws SQLException{
		Connection con = null;
		con = DBPool.getConnection();
		con.setAutoCommit(false);
		java.sql.Statement st1 = con.createStatement();
		//2013-05-20 21:10:12
		if (con != null)
		{System.out.println("connection get....................ok");}
		//删除本城市记录然后插入新
		 st1.addBatch("delete from updaterecord where cityCode ='"+cityCode+"')");
		
		 st1.addBatch("insert into updaterecord(times,cityCode)"
				+ " values('"+Timestamp.valueOf(new Date().toLocaleString())+"',"+cityCode+")");
		// con.commit();
		 st1.executeBatch(); 
         con.commit(); 
         st1.close();
		 con.close();
	}
	
	
	private static void jdbcCreatePlanRooms() throws SQLException{
		
		List<City> clist=HotelPriceInfoAdapter.getCitiesInAllRooms();
		Connection con = null;
		con = DBPool.getConnection();
		con.setAutoCommit(false);
		java.sql.Statement st1 = null;
		if (con != null)
		{System.out.println("connection get....................ok");}
		
		
		st1 = con.createStatement();
		for(int i=0;i<clist.size();i++){
		st1.executeUpdate("CREATE TABLE `priceplan"+clist.get(i).getCity()+"` (`id` int(10) unsigned NOT NULL AUTO_INCREMENT,"+
				  "`hotelCode` varchar(45) NOT NULL DEFAULT '',`ratePlanCategory` varchar(45) NOT NULL DEFAULT '',"+
				  "`marketCode` varchar(45) DEFAULT NULL,`minAdvancedBookingOffset` varchar(45) DEFAULT NULL,"+
				  "`starttime` varchar(45) DEFAULT NULL,`endtime` varchar(45) DEFAULT NULL,"+
				  "`status` varchar(45) DEFAULT NULL,`amountBeforeTax` varchar(8) DEFAULT NULL,"+
				  "`currencyCode` varchar(45) DEFAULT NULL,`listPrice` varchar(45) DEFAULT NULL,"+
				  "`numberOfGuests` varchar(3) DEFAULT NULL,`cancelPenalty` varchar(45) DEFAULT NULL,"+
				  "`cancelPenaltyStart` varchar(45) DEFAULT NULL COMMENT 'can cancel time',"+
				  "`cancelPenaltyEnd` varchar(45) DEFAULT NULL COMMENT 'leave time',"+
				  "`mealsIncluded` varchar(45) DEFAULT NULL,"+
				  "`rebatePromotionStart` varchar(10) DEFAULT NULL,"+
				  "`rebatePromotionEnd` varchar(10) DEFAULT NULL,"+
				  "`rebatePromotionName` varchar(45) DEFAULT NULL,"+
				  "`rebatePromotionCode` varchar(45) DEFAULT NULL,"+
				  "`rebatePromotionCurrencyCode` varchar(45) DEFAULT NULL,"+
				  "`rebatePromotionCurrencyAmount` varchar(45) DEFAULT NULL,"+
				  "`rebatePromotionDesc` varchar(45) DEFAULT NULL,"+
				  "`ratePlanCode` varchar(45) NOT NULL DEFAULT '',"+
				  "`guaranteeCode` varchar(45) DEFAULT NULL,"+
				  "`updatePlanTime` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',"+
				  "`hotelCityCode` varchar(45) DEFAULT NULL,"+
				  "PRIMARY KEY (`id`)"+
				") ENGINE=InnoDB AUTO_INCREMENT=149 DEFAULT CHARSET=utf8;");
		
		
		}
		con.commit();
		st1.close();
		con.close();
		

	}
	
	public static void main(String[] args) throws SQLException,
			InterruptedException, JDOMException, IOException, ParseException {
		  
	System.out.println(new Date().toString());
		//先创建所需的表
		//HotelPriceInfoAdapter.jdbcCreatePlanRooms();
		//HotelPriceInfoAdapter.sychronizePlanRooms("2013-05-12","2013-06-12");//插入数据后会记录每个城市最终的更新日期
	HotelPriceInfoAdapter.insertLastUpdateTime("1");
	
	}

}
